------------------------------------------------------------------------------
-- **************************************************************************
--    Project Name:    OSS-末端出仓-入库时效
--    Job Name:
--    Description :   拉取T-1的代理区入库数据，分析派件时长。
--    Author :        郭瑞玲
--    date：          2022/7/12
-- **************************************************************************
--    Relation ：
--   jms_dm.dm_waybill_reback_sender_detail_dt << [
--     jms_dwd.dwd_tab_barscan_deliver_base_dt,
--     jms_dwd.dwd_tab_barscan_collect_base_dt,
--     jms_dim.dim_network_whole_massage,
--  ]
-- **************************************************************************
--    modify by
-- **************************************************************************
-------------------------------------------------------------------------------
with tab_collect as (  --t-1入库扫描
select
waybill_no ,
scan_site_code,
scan_time
from
(
select
waybill_no ,
scan_site_code,
scan_time ,
ROW_NUMBER() over(partition by waybill_no,scan_site_code order by scan_time,input_time desc) rk
from jms_dwd.dwd_tab_barscan_collect_base_dt
where dt between date_add('{{ execution_date | cst_ds }}',-1) and  '{{ execution_date | cst_ds }}'
and scan_type ='入库扫描'
)t where rk=1 ),
tab_deliver as ( --t-1出仓扫描
select
waybill_no ,
scan_site_code,
scan_time
from
(
select
waybill_no ,
scan_site_code,
scan_time ,
ROW_NUMBER() over(partition by waybill_no,scan_site_code order by scan_time,input_time desc) rk
from jms_dwd.dwd_tab_barscan_deliver_base_dt
where dt between date_add('{{ execution_date | cst_ds }}',-3) and  '{{ execution_date | cst_ds }}'
)t where rk=1
),
tab_detail as ( --入库和出仓关联后明细
select
tab_collect.waybill_no ,
tab_collect.scan_site_code,
tab_collect.scan_time as in_san_time,  --入库扫描时间
tab_deliver.scan_time as deliver_scan_time, --出仓扫描时间
cast((unix_timestamp(tab_collect.scan_time) - unix_timestamp(tab_deliver.scan_time))/3600 as decimal(10,2)) as time_diff_h,  --入库-出仓时间差
net.agent_code,
net.agent_name
from tab_collect
inner join tab_deliver
on tab_collect.waybill_no = tab_deliver.waybill_no
and tab_collect.scan_site_code =tab_deliver.scan_site_code
inner join jms_dim.dim_network_whole_massage net on net.code=tab_collect.scan_site_code
where date(tab_collect.scan_time)='{{ execution_date | cst_ds }}'
),
tab_detail2 as  --添加全网明细、对 时长类型判断、开窗取得代理区入库票数 、代理区入库时长
(
select
date(in_san_time) in_san_date,
waybill_no ,
scan_site_code,
in_san_time,
deliver_scan_time,
time_diff_h,
agent_code,
agent_name,
count(1) over(partition by agent_code,date(in_san_time)) as agent_in_cnt, --代理区入库数
sum(time_diff_h) over(partition by agent_code,date(in_san_time)) as sgent_diff_h_cnt, --入库时长
case when time_diff_h<=1 then '0-1h'
     when time_diff_h >1 and time_diff_h<=2 then '1-2h'
     when time_diff_h >2 and time_diff_h<=3 then '2-3h'
     when time_diff_h >3 and time_diff_h<=4 then '3-4h'
     when time_diff_h >4 and time_diff_h<=5 then '4-5h'
     else '>5h'
end as time_diff_type
from tab_detail
union ALL
select
date(in_san_time) in_san_date,
waybill_no ,
scan_site_code,
in_san_time,
deliver_scan_time,
time_diff_h,
'全网' as agent_code,
'全网' as agent_name,
sum(1) over(partition by date(in_san_time)) as agent_in_cnt, --代理区入库数
sum(time_diff_h) over(partition by date(in_san_time)) as sgent_diff_h_cnt, --入库时长
case when time_diff_h<=1 then '0-1h'
     when time_diff_h >1 and time_diff_h<=2 then '1-2h'
     when time_diff_h >2 and time_diff_h<=3 then '2-3h'
     when time_diff_h >3 and time_diff_h<=4 then '3-4h'
     when time_diff_h >4 and time_diff_h<=5 then '4-5h'
     else '>5h'
end as time_diff_type
from tab_detail
)

insert overwrite table jms_dm.dm_end_deliver_warehouseing_prescription_dt partition(dt)
select
in_san_date,
agent_code,
agent_name,
agent_in_cnt,  --代理区入库总票数
cast(sgent_diff_h_cnt/agent_in_cnt as decimal(10,2)) avg_dliver_h, --平均派件时长
sum(diff_1h_cnt) as diff_1h_cnt, --0-1h（包括1h）票数
cast(sum(diff_1h_cnt)/agent_in_cnt as decimal(10,2)) as diff_1h_rt , --0-1h（包括1h）占比
sum(diff_2h_cnt) as diff_2h_cnt,
cast(sum(diff_2h_cnt)/agent_in_cnt as decimal(10,2)) as diff_2h_rt , --1-2h（包括1h）占比
sum(diff_3h_cnt) as diff_3h_cnt,
cast(sum(diff_3h_cnt)/agent_in_cnt as decimal(10,2)) as diff_3h_rt , --2-3h（包括1h）占比
sum(diff_4h_cnt) as diff_4h_cnt,
cast(sum(diff_4h_cnt)/agent_in_cnt as decimal(10,2)) as diff_4h_rt , --3-4h（包括1h）占比
sum(diff_5h_cnt) as diff_5h_cnt,
cast(sum(diff_5h_cnt)/agent_in_cnt as decimal(10,2)) as diff_5h_rt , --4-5h（包括1h）占比
sum(diff_gre_5h_cnt) as diff_gre_5h_cnt,
cast(sum(diff_gre_5h_cnt)/agent_in_cnt as decimal(10,2)) as diff_gre_5h_rt,  --4-5h（包括1h）占比
in_san_date as dt
from
(
select
in_san_date,
agent_code,
agent_name,
time_diff_type,
agent_in_cnt,
sgent_diff_h_cnt,
if(time_diff_type='0-1h',diff_type_cnt,0) as diff_1h_cnt,
if(time_diff_type='1-2h',diff_type_cnt,0) as diff_2h_cnt,
if(time_diff_type='2-3h',diff_type_cnt,0) as diff_3h_cnt,
if(time_diff_type='3-4h',diff_type_cnt,0) as diff_4h_cnt,
if(time_diff_type='4-5h',diff_type_cnt,0) as diff_5h_cnt,
if(time_diff_type='>5h',diff_type_cnt,0) as diff_gre_5h_cnt
from
(
select
in_san_date,
agent_code,--代理区
agent_name,
time_diff_type, --时长类型
count(1) diff_type_cnt,  --时长类型_单量
agent_in_cnt,
sgent_diff_h_cnt
from tab_detail2 group by agent_code,time_diff_type,agent_in_cnt,sgent_diff_h_cnt,agent_name,in_san_date
)t1
)t2
group by agent_code,agent_in_cnt,sgent_diff_h_cnt,agent_name,in_san_date
